﻿CREATE PROCEDURE [dbo].[spMaint_Security]
(
	@reportOnly		bit		= 0
)
AS

SET NOCOUNT ON;

BEGIN --- Declare and initialize local variables

IF OBJECT_ID('tempdb..#tNoPerm_LoginReview') IS NOT NULL --- If the temp table exists, drop it
	DROP TABLE #tNoPerm_LoginReview;

--- Declare Local Variables
DECLARE @sCmd varchar(MAX), @sPerms1 varchar(MAX), @sPerms2 varchar(MAX), @sDropLoginsSQL varchar(MAX),
		@sDropDbUsersNoRights varchar(MAX), @sStandardLogins varchar(MAX), @isExecute bit,
		@sSqlAgentUser varchar(256), @sSqlServiceUser varchar(256), @sAlterLogin varchar(MAX),
		@sUpdateLogin varchar(MAX), @LoginName varchar(256);
DECLARE	@sTemplateSrvRoleMember varchar(MAX), @sTemplateDbRoleMember varchar(MAX),
		@sTemplateDbCreateUser varchar(MAX), @sTemplateDbAddUserConn varchar(MAX),
		@sTemplateDbGrant varchar(MAX), @sTemplateCreateLogin varchar(MAX),
		@sTemplateServerGrant varchar(MAX), @sTemplateObjectExists varchar(MAX),
		@sTemplateSchemaExists varchar(MAX), @sTemplateEndpointExists varchar(MAX),
		@sTemplateSQLloginPwd varchar(MAX)
SELECT	@sTemplateSrvRoleMember = 'USE [master]; EXEC [dbo].[sp_addsrvrolemember] @loginame = ''[VAR_USERNAME]'', @rolename = ''[VAR_ROLENAME]'';',
		@sTemplateDbRoleMember = 'USE [[VAR_DBNAME]]; IF EXISTS (SELECT TOP 1 [name] FROM [sys].[database_principals] WHERE [name] = ''[VAR_ROLENAME]'' AND [type] = ''R'') EXEC [dbo].[sp_addrolemember] @membername = ''[VAR_USERNAME]'', @rolename = ''[VAR_ROLENAME]'';',
		@sTemplateDbCreateUser = 'IF NOT EXISTS (SELECT TOP 1 [name] FROM [[VAR_DBNAME]].[sys].[database_principals] WHERE [name] = ''[VAR_USERNAME]'') CREATE USER [[VAR_USERNAME]] FROM LOGIN [[VAR_USERNAME]];',
		@sTemplateDbAddUserConn = 'USE [[VAR_DBNAME]];[VAR_CREATEUSER][VAR_GRANTCMD] TO [[VAR_USERNAME]];',
		@sTemplateDbGrant = 'USE [[VAR_DBNAME]];[VAR_OBJECTEXISTS][VAR_GRANTCMD] TO [[VAR_USERNAME]];',
		@sTemplateCreateLogin = 'USE [master];[VAR_CREATELOGIN][VAR_GRANTCMD] TO [[VAR_USERNAME]];',
		@sTemplateServerGrant = 'IF NOT EXISTS (SELECT TOP 1 [name] FROM [sys].[server_principals] WHERE [name] = ''[VAR_USERNAME]'') CREATE LOGIN [[VAR_USERNAME]] [VAR_LOGINOPTIONS];',
		@sTemplateObjectExists = 'IF EXISTS (SELECT TOP 1 [name] FROM [sys].[objects] WHERE (QUOTENAME(SCHEMA_NAME([schema_id])) + ''.'' + QUOTENAME([name])) = ''[VAR_OBJECTNAME]'') ',
		@sTemplateSchemaExists = 'IF EXISTS (SELECT TOP 1 [name] FROM [sys].[schemas] WHERE [name] = ''[VAR_OBJECTNAME]'') ',
		@sTemplateEndpointExists = 'IF EXISTS (SELECT TOP 1 [name] FROM [sys].[tcp_endpoints] WHERE [name] = ''[VAR_OBJECTNAME]'') ',
		@sTemplateSQLloginPwd = 'ALTER LOGIN [VAR_LOGINNAME] WITH PASSWORD = [VAR_PASSWORD] HASHED, CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF;'
SELECT	@sStandardLogins = '$(StandardLogins)',
		@sPerms1 = 'USE [?];
IF EXISTS (SELECT TOP 1 [name] FROM [sys].[databases] WHERE [name] = DB_NAME() AND [source_database_id] IS NULL AND [is_read_only] = 0)
	SELECT		t1.[name] [userName],
				t3.[name] [dbRole],
				NULL [dbPermission],
				DB_NAME() [dbName]
	FROM		[sys].[database_principals] t1
	JOIN		[sys].[database_role_members] t2 ON t1.[principal_id] = t2.[member_principal_id]
	JOIN		[sys].[database_principals] t3 ON t2.[role_principal_id] = t3.[principal_id]
	WHERE		t1.[type] <> ''R''			--- Roles are defined in the dbProject
				AND t1.[type] <> ''A''		--- App roles are defined by the vendor or in the dbProject
				AND t1.[name] NOT IN (''dbo'', ''MS_DataCollectorInternalUser'')
				AND t1.[name] NOT LIKE ''##MS%''
	ORDER BY	t1.[name];',
		@sPerms2 = 'USE [?];
IF EXISTS (SELECT TOP 1 [name] FROM [sys].[databases] WHERE [name] = DB_NAME() AND [source_database_id] IS NULL AND [is_read_only] = 0)
	SELECT		t1.[name] [userName],
				NULL [dbRole],
				t2.[state_desc] + '' '' + t2.[permission_name] + (
					CASE	WHEN t2.[class_desc] = ''OBJECT_OR_COLUMN'' COLLATE SQL_Latin1_General_CP1_CI_AS THEN '' ON '' + QUOTENAME(SCHEMA_NAME(t3.[schema_id])) + ''.'' + QUOTENAME(t3.[name]) COLLATE SQL_Latin1_General_CP1_CI_AS
							WHEN t2.[class_desc] = ''SYMMETRIC_KEYS'' COLLATE SQL_Latin1_General_CP1_CI_AS THEN '' ON SYMMETRIC KEY::'' + QUOTENAME(t4.[name]) COLLATE SQL_Latin1_General_CP1_CI_AS
							WHEN t2.[class_desc] = ''CERTIFICATE'' COLLATE SQL_Latin1_General_CP1_CI_AS THEN '' ON CERTIFICATE::'' + QUOTENAME(t5.[name]) COLLATE SQL_Latin1_General_CP1_CI_AS
							WHEN t2.[class_desc] = ''SCHEMA'' COLLATE SQL_Latin1_General_CP1_CI_AS THEN '' ON SCHEMA::'' + QUOTENAME(t6.[name]) COLLATE SQL_Latin1_General_CP1_CI_AS
							ELSE ''''
							END) [dbPermission],
				DB_NAME() [dbName]
	FROM		[sys].[database_principals] t1
	JOIN		[sys].[database_permissions] t2 ON t1.[principal_id] = t2.[grantee_principal_id]
	LEFT JOIN	[sys].[objects] t3 ON t2.[major_id] = t3.[object_id]
				AND t2.[class_desc] = ''OBJECT_OR_COLUMN''
	LEFT JOIN	[sys].[symmetric_keys] t4 ON t2.[major_id] = t4.[symmetric_key_id]
				AND t2.[class_desc] = ''SYMMETRIC_KEYS''
	LEFT JOIN	[sys].[certificates] t5 ON t2.[major_id] = t5.[certificate_id]
				AND t2.[class_desc] = ''CERTIFICATE''
	LEFT JOIN	[sys].[schemas] t6 ON t2.[major_id] = t6.[schema_id]
				AND t2.[class_desc] = ''SCHEMA''
	WHERE		t1.[type] <> ''C''			--- C = certificate
				AND t1.[type] <> ''R''		--- Roles are defined in the dbProject
				AND t1.[type] <> ''A''		--- App roles are defined by the vendor or in the dbProject
				AND t2.[major_id] >= 0		--- negative ids are native to SQL Server
				AND t1.[principal_id] > 4	--- skip built-in users
				AND t1.[name] NOT IN (''dbo'', ''MS_DataCollectorInternalUser'')
				AND t1.[name] NOT LIKE ''##MS%''
	ORDER BY	t1.[name];',
		@sDropLoginsSQL = 'USE [?];
IF DB_NAME() NOT IN (''master'', ''model'', ''tempdb'', ''msdb'', ''centraldba'')
	AND EXISTS (SELECT TOP 1 [name] FROM [sys].[databases] WHERE [name] = DB_NAME() AND [source_database_id] IS NULL AND [is_read_only] = 0)
BEGIN
	DELETE		t1
	FROM		#tNoPerm_LoginReview t1
	JOIN		[sys].[database_principals] t2 ON t1.[sid] = t2.[sid];

	DELETE		t1
	FROM		#tNoPerm_LoginReview t1
	JOIN		(
				SELECT		ta.[sid]
				FROM		[sys].[database_principals] ta
				JOIN		[sys].[database_permissions] tb ON ta.[principal_id] = tb.[grantee_principal_id]
				) t2 ON t1.[sid] = t2.[sid];
END',
		@sDropDbUsersNoRights = REPLACE('DECLARE @sCmd varchar(MAX)
USE [?];
IF DB_NAME() NOT IN (''master'', ''model'', ''tempdb'', ''msdb'', ''centraldba'')
	AND EXISTS (SELECT TOP 1 [name] FROM [sys].[databases] WHERE [name] = DB_NAME() AND [source_database_id] IS NULL AND [is_read_only] = 0)
BEGIN
	DECLARE crsDropUser CURSOR READ_ONLY FAST_FORWARD FOR
		SELECT		REPLACE(''USE [?]; IF EXISTS (SELECT TOP 1 [name] FROM [sys].[schemas] WHERE [name] = ''''[VAR_USERNAME]'''') BEGIN DROP SCHEMA [[VAR_USERNAME]]; END DROP USER [[VAR_USERNAME]];'', ''[VAR_USERNAME]'', t1.[name])
		FROM		[sys].[database_principals] t1
		LEFT JOIN	[sys].[database_role_members] t2 ON t1.[principal_id] = t2.[member_principal_id]
		LEFT JOIN	[sys].[database_permissions] t3 ON t1.[principal_id] = t3.[grantee_principal_id]
		WHERE		t2.[member_principal_id] IS NULL
					AND t3.[grantee_principal_id] IS NULL
					AND t1.[type] <> ''R''
					AND ISNULL(t1.[sid], 0x00) <> 0x00
	OPEN crsDropUser
	FETCH NEXT FROM crsDropUser INTO @sCmd
	WHILE @@FETCH_STATUS <> -1
	BEGIN
		RAISERROR(@sCmd, 0, 1) WITH NOWAIT
		IF ISNULL([VAR_REPORTONLY], 0) = 0
			EXEC (@sCmd)
		FETCH NEXT FROM crsDropUser INTO @sCmd
	END
	CLOSE crsDropUser
	DEALLOCATE crsDropUser
END
', '[VAR_REPORTONLY]', CAST(@reportOnly as varchar(1000)));
DECLARE @tStandardLogins TABLE
(
	[id] int IDENTITY(1,1) PRIMARY KEY,
	[loginName] varchar(256)
)
DECLARE @tDbPerms TABLE
(
	[id] int IDENTITY(1,1) PRIMARY KEY,
	[dbName] varchar(256) NOT NULL,
	[userName] varchar(256) NOT NULL,
	[dbRole] varchar(256) NULL,
	[dbPermission] varchar(2000) NULL,
	[SqlCommand]  AS (case when [dbRole] IS NOT NULL AND [dbName] IS NOT NULL then replace(replace(replace('USE [[VAR_DBNAME]]; EXEC [dbo].[sp_addrolemember] @membername = ''[VAR_USERNAME]'', @rolename = ''[VAR_ROLENAME]'';','[VAR_USERNAME]',[userName]),'[VAR_ROLENAME]',[dbRole]),'[VAR_DBNAME]',[dbName]) when [dbPermission] IS NOT NULL AND [dbName] IS NOT NULL then replace(replace(replace('USE [[VAR_DBNAME]]; [VAR_GRANTCMD] TO [[VAR_USERNAME]];','[VAR_USERNAME]',[userName]),'[VAR_GRANTCMD]',[dbPermission]),'[VAR_DBNAME]',[dbName]) else '' end)
)
DECLARE @tSrvPerms TABLE
(
	[id] int IDENTITY(1,1) PRIMARY KEY,
	[userName] varchar(256) NOT NULL,
	[sid] varbinary(256) NULL,
	[serverRole] varchar(256) NULL,
	[serverPermission] varchar(2000) NULL, 
	[SqlCommand]  AS (case when [serverRole] IS NOT NULL then replace(replace('USE [master]; EXEC [dbo].[sp_addsrvrolemember] @loginame = ''[VAR_USERNAME]'', @rolename = ''[VAR_ROLENAME]'';','[VAR_USERNAME]',[userName]),'[VAR_ROLENAME]',[serverRole]) when [serverPermission] IS NOT NULL then replace(replace('USE [master]; [VAR_GRANTCMD] TO [[VAR_USERNAME]];','[VAR_USERNAME]',[userName]),'[VAR_GRANTCMD]',[serverPermission]) else '' end)
)
CREATE TABLE #tNoPerm_LoginReview (
	[permId] int IDENTITY(1,1) PRIMARY KEY,
	[userName] varchar(256) NOT NULL,
	[sid] varbinary(256) NOT NULL
)

--- Split the "standard logins" csv into table records
INSERT INTO @tStandardLogins
	SELECT			DISTINCT
					LTRIM(RTRIM(logns.a.value('.', 'sysname'))) loginName
	FROM			(
					SELECT		CAST('<A>' + REPLACE(@sStandardLogins, ',', '</A><A>') + '</A>' AS XML) logins
					) t1
	CROSS APPLY		logins.nodes('/A') logns(a)
	WHERE			LTRIM(RTRIM(logns.a.value('.', 'sysname'))) <> ''

--- Add service accounts to the standard logins
EXEC	[master].[dbo].[xp_regread]	'HKEY_LOCAL_MACHINE', 'SYSTEM\CurrentControlSet\services\SQLSERVERAGENT', 'ObjectName',
									@sSqlAgentUser OUTPUT;
EXEC	[master].[dbo].[xp_regread]	'HKEY_LOCAL_MACHINE', 'SYSTEM\CurrentControlSet\services\MSSQLSERVER', 'ObjectName',
									@sSqlServiceUser OUTPUT;

INSERT INTO @tStandardLogins
	SELECT	@sSqlAgentUser [userName] WHERE @sSqlAgentUser IS NOT NULL
	UNION
	SELECT	@sSqlServiceUser WHERE @sSqlServiceUser IS NOT NULL

END

BEGIN --- Safety Net for Non-configured servers

IF NOT EXISTS (
	SELECT		TOP 1 [secUserName]
	FROM		[dbo].[tblMaint_Security]
	WHERE		[secUserName] NOT IN (
				SELECT		t1.[name] [loginName]
				FROM		[sys].[server_principals] t1
				JOIN		[sys].[server_role_members] t2 ON t1.[principal_id] = t2.[member_principal_id]
				JOIN		[sys].[server_principals] t3 ON t2.[role_principal_id] = t3.[principal_id]
				WHERE		t1.[type] <> 'C'
							AND (
								t1.[name] IN (SELECT [loginName] FROM @tStandardLogins)
								OR t1.[name] IN ('public')
								OR t1.[name] LIKE '##%'
								OR t1.[name] LIKE 'NT SERVICE\%'
								OR t1.[name] LIKE 'NT AUTHORITY\%'
							)

				UNION

				SELECT		t1.[name] [loginName]
				FROM		[sys].[server_principals] t1
				JOIN		[sys].[server_permissions] t2 ON t1.[principal_id] = t2.[grantee_principal_id]
				LEFT JOIN	[sys].[endpoints] t3 ON t2.[major_id] = t3.[endpoint_id]
							AND t2.[class_desc] = 'ENDPOINT'
				LEFT JOIN	[sys].[server_principals] t4 ON t2.[major_id] = t4.[principal_id]
							AND t2.[class_desc] = 'SERVER_PRINCIPAL'
				WHERE		t1.[type] <> 'C'
							AND (
								t1.[name] IN (SELECT [loginName] FROM @tStandardLogins)
								OR t1.[name] IN ('public')
								OR t1.[name] LIKE '##%'
								OR t1.[name] LIKE 'NT SERVICE\%'
								OR t1.[name] LIKE 'NT AUTHORITY\%'
							)
				)
	)
BEGIN
	RAISERROR('This server has not been configured for spMaint_Security', 0, 1) WITH NOWAIT
	RETURN
END

END

BEGIN --- Make sure the table has all permissions from the server level for the "standard logins"
DELETE		Target
FROM		[dbo].[tblMaint_Security] AS Target
LEFT JOIN	(
			SELECT		t1.[name] [loginName],
						t1.[type],
						(CASE WHEN t1.[type] = 'S' THEN t1.[sid] ELSE NULL END) [secSid],
						t3.[name] [serverRole],
						NULL [serverPermission],
						NULL [dbName],
						NULL [dbRole]
			FROM		[sys].[server_principals] t1
			JOIN		[sys].[server_role_members] t2 ON t1.[principal_id] = t2.[member_principal_id]
			JOIN		[sys].[server_principals] t3 ON t2.[role_principal_id] = t3.[principal_id]
			WHERE		t1.[type] <> 'C'
						AND (
							t1.[name] IN (SELECT [loginName] FROM @tStandardLogins)
							OR t1.[name] IN ('public')
							OR t1.[name] LIKE '##%'
							OR t1.[name] LIKE 'NT SERVICE\%'
							OR t1.[name] LIKE 'NT AUTHORITY\%'
						)

			UNION

			SELECT		t1.[name] [loginName],
						t1.[type],
						(CASE WHEN t1.[type] = 'S' THEN t1.[sid] ELSE NULL END) [secSid],
						NULL [serverRole],
						t2.[state_desc] + ' ' + t2.[permission_name] + (
							CASE	WHEN t2.[class_desc] = 'ENDPOINT' COLLATE SQL_Latin1_General_CP1_CI_AS THEN ' ON ENDPOINT::' + QUOTENAME(t3.[name]) COLLATE SQL_Latin1_General_CP1_CI_AS
									WHEN t2.[class_desc] = 'SERVER_PRINCIPAL' COLLATE SQL_Latin1_General_CP1_CI_AS THEN ' ON LOGIN::' + QUOTENAME(t4.[name]) COLLATE SQL_Latin1_General_CP1_CI_AS
									ELSE ''
									END) [server_permission],
						NULL [dbName],
						NULL [dbRole]
			FROM		[sys].[server_principals] t1
			JOIN		[sys].[server_permissions] t2 ON t1.[principal_id] = t2.[grantee_principal_id]
			LEFT JOIN	[sys].[endpoints] t3 ON t2.[major_id] = t3.[endpoint_id]
						AND t2.[class_desc] = 'ENDPOINT'
			LEFT JOIN	[sys].[server_principals] t4 ON t2.[major_id] = t4.[principal_id]
						AND t2.[class_desc] = 'SERVER_PRINCIPAL'
			WHERE		t1.[type] <> 'C'
						AND (
							t1.[name] IN (SELECT [loginName] FROM @tStandardLogins)
							OR t1.[name] IN ('public')
							OR t1.[name] LIKE '##%'
							OR t1.[name] LIKE 'NT SERVICE\%'
							OR t1.[name] LIKE 'NT AUTHORITY\%'
						)
			) AS Source ON Source.[loginName] = Target.[secUserName] COLLATE SQL_Latin1_General_CP1_CI_AS
			AND ISNULL(Source.[serverRole], '') = ISNULL(Target.[secServerRole], '') COLLATE SQL_Latin1_General_CP1_CI_AS
			AND ISNULL(Source.[serverPermission], '') = ISNULL(Target.[secServerPermission], '') COLLATE SQL_Latin1_General_CP1_CI_AS
			AND ISNULL(Source.[dbName], '') = ISNULL(Target.[secDbName], '') COLLATE SQL_Latin1_General_CP1_CI_AS
			AND ISNULL(Source.[dbRole], '') = ISNULL(Target.[secDbRole], '') COLLATE SQL_Latin1_General_CP1_CI_AS
			AND ISNULL(Source.[secSid], 0x) = ISNULL(Target.[secSid], 0x)
WHERE		Source.[loginName] IS NULL
			AND Target.[secDbProject] = 'SYSTEM'

INSERT INTO [dbo].[tblMaint_Security]
(
	[secDbProject],
	[secUserName],
	[secSid],
	[secServerRole],
	[secServerPermission],
	[secDbName],
	[secDbRole]
)
SELECT		'SYSTEM',
			Source.[loginName],
			Source.[secSid],
			Source.[serverRole],
			Source.[serverPermission],
			Source.[dbName],
			Source.[dbRole]
FROM		[dbo].[tblMaint_Security] AS Target
RIGHT JOIN	(
			SELECT		t1.[name] [loginName],
						t1.[type],
						(CASE WHEN t1.[type] = 'S' THEN t1.[sid] ELSE NULL END) [secSid],
						t3.[name] [serverRole],
						NULL [serverPermission],
						NULL [dbName],
						NULL [dbRole]
			FROM		[sys].[server_principals] t1
			JOIN		[sys].[server_role_members] t2 ON t1.[principal_id] = t2.[member_principal_id]
			JOIN		[sys].[server_principals] t3 ON t2.[role_principal_id] = t3.[principal_id]
			WHERE		t1.[type] <> 'C'
						AND (
							t1.[name] IN (SELECT [loginName] FROM @tStandardLogins)
							OR t1.[name] IN ('public')
							OR t1.[name] LIKE '##%'
							OR t1.[name] LIKE 'NT SERVICE\%'
							OR t1.[name] LIKE 'NT AUTHORITY\%'
						)

			UNION

			SELECT		t1.[name] [loginName],
						t1.[type],
						(CASE WHEN t1.[type] = 'S' THEN t1.[sid] ELSE NULL END) [secSid],
						NULL [serverRole],
						t2.[state_desc] + ' ' + t2.[permission_name] + (
							CASE	WHEN t2.[class_desc] = 'ENDPOINT' COLLATE SQL_Latin1_General_CP1_CI_AS THEN ' ON ENDPOINT::' + QUOTENAME(t3.[name]) COLLATE SQL_Latin1_General_CP1_CI_AS
									WHEN t2.[class_desc] = 'SERVER_PRINCIPAL' COLLATE SQL_Latin1_General_CP1_CI_AS THEN ' ON LOGIN::' + QUOTENAME(t4.[name]) COLLATE SQL_Latin1_General_CP1_CI_AS
									ELSE ''
									END) [server_permission],
						NULL [dbName],
						NULL [dbRole]
			FROM		[sys].[server_principals] t1
			JOIN		[sys].[server_permissions] t2 ON t1.[principal_id] = t2.[grantee_principal_id]
			LEFT JOIN	[sys].[endpoints] t3 ON t2.[major_id] = t3.[endpoint_id]
						AND t2.[class_desc] = 'ENDPOINT'
			LEFT JOIN	[sys].[server_principals] t4 ON t2.[major_id] = t4.[principal_id]
						AND t2.[class_desc] = 'SERVER_PRINCIPAL'
			WHERE		t1.[type] <> 'C'
						AND (
							t1.[name] IN (SELECT [loginName] FROM @tStandardLogins)
							OR t1.[name] IN ('public')
							OR t1.[name] LIKE '##%'
							OR t1.[name] LIKE 'NT SERVICE\%'
							OR t1.[name] LIKE 'NT AUTHORITY\%'
						)

			UNION

			SELECT		t1.[name] [loginName],		--- Ignoring any permissions inside of databases
						t1.[type],
						NULL,
						NULL,
						'*',
						'*',
						NULL
			FROM		[sys].[server_principals] t1
			WHERE		t1.[type] <> 'C'
						AND (
							t1.[name] IN (SELECT [loginName] FROM @tStandardLogins)
							OR t1.[name] IN ('public')
							OR t1.[name] LIKE '##%'
							OR t1.[name] LIKE 'NT SERVICE\%'
							OR t1.[name] LIKE 'NT AUTHORITY\%'
						)
			) AS Source ON Source.[loginName] = Target.[secUserName] COLLATE SQL_Latin1_General_CP1_CI_AS
			AND ISNULL(Source.[serverRole], '') = ISNULL(Target.[secServerRole], '') COLLATE SQL_Latin1_General_CP1_CI_AS
			AND ISNULL(Source.[serverPermission], '') = ISNULL(Target.[secServerPermission], '') COLLATE SQL_Latin1_General_CP1_CI_AS
			AND ISNULL(Source.[dbName], '') = ISNULL(Target.[secDbName], '') COLLATE SQL_Latin1_General_CP1_CI_AS
			AND ISNULL(Source.[dbRole], '') = ISNULL(Target.[secDbRole], '') COLLATE SQL_Latin1_General_CP1_CI_AS
			AND ISNULL(Source.[secSid], 0x) = ISNULL(Target.[secSid], 0x)
WHERE		Target.[secUserName] IS NULL

END

BEGIN --- Get the current permissions

INSERT INTO @tSrvPerms--- Get the current server permissions
(
	[serverRole],
	[userName],
	[sid],
	[serverPermission]
)
SELECT		t1.[name] [serverRole],
			t3.[name] [userName],
			(CASE WHEN t1.[type] = 'S' THEN t1.[sid] ELSE NULL END) [sid],
			NULL [serverPermission]
FROM		[sys].[server_principals] t1
JOIN		[sys].[server_role_members] t2 ON t1.[principal_id] = t2.[role_principal_id]
JOIN		[sys].[server_principals] t3 ON t2.[member_principal_id] = t3.[principal_id]
WHERE		t3.[name] NOT IN ('sa')

UNION

SELECT		NULL [serverRole],
			t1.[name] [loginName],
			(CASE WHEN t1.[type] = 'S' THEN t1.[sid] ELSE NULL END) [sid],
			t2.[state_desc] + ' ' + t2.[permission_name] + (
				CASE	WHEN t2.[class_desc] = 'ENDPOINT' COLLATE SQL_Latin1_General_CP1_CI_AS THEN ' ON ENDPOINT::' + QUOTENAME(t3.[name]) COLLATE SQL_Latin1_General_CP1_CI_AS
						WHEN t2.[class_desc] = 'SERVER_PRINCIPAL' COLLATE SQL_Latin1_General_CP1_CI_AS THEN ' ON LOGIN::' + QUOTENAME(t4.[name]) COLLATE SQL_Latin1_General_CP1_CI_AS
						ELSE ''
						END) [serverPermission]
FROM		[sys].[server_principals] t1
JOIN		[sys].[server_permissions] t2 ON t1.[principal_id] = t2.[grantee_principal_id]
LEFT JOIN	[sys].[endpoints] t3 ON t2.[major_id] = t3.[endpoint_id]
			AND t2.[class_desc] = 'ENDPOINT'
LEFT JOIN	[sys].[server_principals] t4 ON t2.[major_id] = t4.[principal_id]
			AND t2.[class_desc] = 'SERVER_PRINCIPAL'
WHERE		t1.[type] <> 'C'
			AND t1.[name] NOT IN ('sa')
			AND t2.[major_id] >= 0

--- Get the current database permissions
INSERT INTO @tDbPerms
(
	[userName],
	[dbRole],
	[dbPermission],
	[dbName]
)
EXEC	[dbo].[sp_MSforeachdb] @sPerms1

INSERT INTO @tDbPerms
(
	[userName],
	[dbRole],
	[dbPermission],
	[dbName]
)
EXEC	[dbo].[sp_MSforeachdb] @sPerms2

END

BEGIN --- Add missing Server permissions

RAISERROR ('Add missing Server permissions', 0, 1) WITH NOWAIT
DECLARE crsAdd CURSOR FOR
	SELECT		[addCmd]
	FROM		(
				SELECT		DISTINCT
							(CASE	WHEN [secServerRole] IS NOT NULL THEN
										REPLACE(
											REPLACE(@sTemplateSrvRoleMember, '[VAR_USERNAME]', [secUserName])
										, '[VAR_ROLENAME]', [secServerRole])
									WHEN [secDbRole] IS NOT NULL AND [secDbName] IS NOT NULL THEN 
										REPLACE(
											REPLACE(
												REPLACE(@sTemplateDbRoleMember, '[VAR_USERNAME]', [secUserName])
											, '[VAR_ROLENAME]', [secDbRole])
										, '[VAR_DBNAME]', [secDbName])
									WHEN [secServerPermission] = 'GRANT CONNECT' AND [secDbName] IS NOT NULL THEN
										REPLACE(
											REPLACE(
												REPLACE(
													REPLACE(@sTemplateDbAddUserConn, '[VAR_CREATEUSER]', @sTemplateDbCreateUser)
												, '[VAR_USERNAME]', [secUserName])
											, '[VAR_GRANTCMD]', [secServerPermission])
										, '[VAR_DBNAME]' ,[secDbName])
									WHEN [secServerPermission] IS NOT NULL AND [secDbName] IS NOT NULL THEN
										REPLACE(
											REPLACE(
												REPLACE(
													REPLACE(@sTemplateDbGrant, '[VAR_OBJECTEXISTS]', (CASE
														WHEN [secObjectType] = 'SCHEMA' THEN REPLACE(@sTemplateSchemaExists, '[VAR_OBJECTNAME]', [secObjectName])
														WHEN [secObjectType] = 'OBJECT' THEN REPLACE(@sTemplateObjectExists, '[VAR_OBJECTNAME]', [secObjectName])
														ELSE ''
														END))
												, '[VAR_USERNAME]', [secUserName])
											, '[VAR_GRANTCMD]', [secServerPermission])
										, '[VAR_DBNAME]', [secDbName])
									WHEN [secServerPermission] = 'GRANT CONNECT SQL' THEN
										REPLACE(
											REPLACE(
												REPLACE(
													REPLACE(@sTemplateCreateLogin, '[VAR_CREATELOGIN]', @sTemplateServerGrant)
												, '[VAR_LOGINOPTIONS]', (CASE
															WHEN [secSid] IS NULL AND [secHshPwd] IS NULL THEN ' FROM WINDOWS;'
															WHEN [secSid] IS NOT NULL AND [secHshPwd] IS NOT NULL THEN REPLACE(REPLACE(' WITH PASSWORD = ''[VAR_HSHPWD]'' HASHED, SID=[VAR_SID],CHECK_EXPIRATION=OFF,CHECK_POLICY=OFF;', '[VAR_SID]', CONVERT(varchar(1024), [secSid], 1)), '[VAR_HSHPWD]', CONVERT(varchar(1024), [secHshPwd], 1))
															WHEN [secSid] IS NULL AND [secHshPwd] IS NOT NULL THEN REPLACE(' WITH PASSWORD = [VAR_HSHPWD] HASHED, CHECK_EXPIRATION=OFF,CHECK_POLICY=OFF;', '[VAR_HSHPWD]', CONVERT(varchar(1024), [secHshPwd], 1))
															ELSE REPLACE(' WITH PASSWORD = ''Number@1'', SID=[VAR_SID],CHECK_EXPIRATION=OFF,CHECK_POLICY=OFF;', '[VAR_SID]', CONVERT(varchar(1024), [secSid], 1))
															END))
											, '[VAR_USERNAME]', [secUserName])
										, '[VAR_GRANTCMD]', [secServerPermission])
									WHEN [secServerPermission] IS NOT NULL THEN
										REPLACE(
											REPLACE(
												REPLACE(
													REPLACE(@sTemplateDbGrant, '[VAR_OBJECTEXISTS]', (CASE
														WHEN [secObjectType] = 'ENDPOINT' THEN REPLACE(@sTemplateEndpointExists, '[VAR_OBJECTNAME]', [secObjectName])
														WHEN [secObjectType] = 'SCHEMA' THEN REPLACE(@sTemplateSchemaExists, '[VAR_OBJECTNAME]', [secObjectName])
														WHEN [secObjectType] = 'OBJECT' THEN REPLACE(@sTemplateObjectExists, '[VAR_OBJECTNAME]', [secObjectName])
														ELSE ''
														END))
												, '[VAR_USERNAME]', [secUserName])
											, '[VAR_GRANTCMD]', [secServerPermission])
										, '[VAR_DBNAME]', 'master')
									ELSE ''
									END) [addCmd],
							ROW_NUMBER() OVER (ORDER BY
												(CASE	WHEN [secServerPermission] = 'GRANT CONNECT SQL' THEN 0
														WHEN [secServerPermission] IS NOT NULL AND [secDbName] IS NULL THEN 1
														WHEN [secServerRole] IS NOT NULL THEN 2
														WHEN [secServerPermission] IS NOT NULL AND [secDbName] IS NOT NULL THEN 3
														WHEN [secDbName] IS NOT NULL AND [secDbRole] IS NULL THEN 4
														ELSE 10
														END),
												[secServerRole],
												[secServerPermission],
												[secDbName],
												[secDbRole],
												[secUserName]
								) [rowOrder]
				FROM		[dbo].[tblMaint_Security] t1
				LEFT JOIN	@tSrvPerms t2 ON t1.[secUserName] = t2.[userName]
							AND ISNULL(t1.[secServerRole], '') = ISNULL(t2.[serverRole], '')
							AND ISNULL(t1.[secServerPermission], '') = ISNULL(t2.[serverPermission], '')
				WHERE		t2.[userName] IS NULL
							AND t1.[secDbName] IS NULL
							AND t1.[secDbRole] IS NULL
				) t1
	GROUP BY	[addCmd]
	ORDER BY	MIN([rowOrder])
OPEN crsAdd
FETCH NEXT FROM crsAdd INTO @sCmd
WHILE @@FETCH_STATUS <> -1
  BEGIN
	RAISERROR(@sCmd, 0, 1) WITH NOWAIT
	IF ISNULL(@reportOnly, 0) = 0
		EXEC	(@sCmd)

	FETCH NEXT FROM crsAdd INTO @sCmd
  END
CLOSE crsAdd
DEALLOCATE crsAdd

END

BEGIN --- Remove unapproved/extra Server permissions

RAISERROR ('Remove unapproved/extra Server permissions', 0, 1) WITH NOWAIT
DECLARE crsRemove CURSOR FOR
	SELECT		REPLACE(REPLACE(t2.SqlCommand, 'sp_add', 'sp_drop'), 'GRANT', 'REVOKE') [dropCmd]
	FROM		[dbo].[tblMaint_Security] t1
	RIGHT JOIN	@tSrvPerms t2 ON t1.[secUserName] = t2.[userName]
				AND ISNULL(t1.[secServerRole], '') = ISNULL(t2.[serverRole], '')
				AND ISNULL(t1.[secServerPermission], '') = ISNULL(t2.[serverPermission], '')
	WHERE		t1.[secUserName] IS NULL
				AND t1.[secDbName] IS NULL
				AND t1.[secDbRole] IS NULL
	ORDER BY	(CASE	WHEN [secServerRole] IS NOT NULL THEN 1
						WHEN [secServerPermission] IS NOT NULL AND [secDbName] IS NULL THEN 2
						WHEN [secServerPermission] IS NOT NULL AND [secDbName] IS NOT NULL THEN 3
						WHEN [secDbName] IS NOT NULL AND [secDbRole] IS NULL THEN 4
						ELSE 10
						END),
				[secServerRole],
				[secServerPermission],
				[secDbName],
				[secDbRole],
				[secUserName];
OPEN crsRemove
FETCH NEXT FROM crsRemove INTO @sCmd
WHILE @@FETCH_STATUS <> -1
  BEGIN
	RAISERROR(@sCmd, 0, 1) WITH NOWAIT
	IF ISNULL(@reportOnly, 0) = 0
		EXEC	(@sCmd)

	FETCH NEXT FROM crsRemove INTO @sCmd
  END
CLOSE crsRemove
DEALLOCATE crsRemove

END

BEGIN --- Add missing Database permissions

RAISERROR ('Add missing Database permissions', 0, 1) WITH NOWAIT
DECLARE crsAdd CURSOR FOR
	SELECT		(CASE	WHEN [secServerRole] IS NOT NULL THEN
							REPLACE(
								REPLACE(@sTemplateSrvRoleMember, '[VAR_USERNAME]', [secUserName])
							, '[VAR_ROLENAME]', [secServerRole])
						WHEN [secDbRole] IS NOT NULL AND [secDbName] IS NOT NULL THEN 
							REPLACE(
								REPLACE(
									REPLACE(@sTemplateDbRoleMember, '[VAR_USERNAME]', [secUserName])
								, '[VAR_ROLENAME]', [secDbRole])
							, '[VAR_DBNAME]', [secDbName])
						WHEN [secServerPermission] = 'GRANT CONNECT' AND [secDbName] IS NOT NULL THEN
							REPLACE(
								REPLACE(
									REPLACE(
										REPLACE(@sTemplateDbAddUserConn, '[VAR_CREATEUSER]', @sTemplateDbCreateUser)
									, '[VAR_USERNAME]', [secUserName])
								, '[VAR_GRANTCMD]', [secServerPermission])
							, '[VAR_DBNAME]' ,[secDbName])
						WHEN [secServerPermission] IS NOT NULL AND [secDbName] IS NOT NULL THEN
							REPLACE(
								REPLACE(
									REPLACE(
										REPLACE(@sTemplateDbGrant, '[VAR_OBJECTEXISTS]', (CASE
											WHEN [secObjectType] = 'SCHEMA' THEN REPLACE(@sTemplateSchemaExists, '[VAR_OBJECTNAME]', [secObjectName])
											WHEN [secObjectType] = 'OBJECT' THEN REPLACE(@sTemplateObjectExists, '[VAR_OBJECTNAME]', [secObjectName])
											ELSE ''
											END))
									, '[VAR_USERNAME]', [secUserName])
								, '[VAR_GRANTCMD]', [secServerPermission])
							, '[VAR_DBNAME]', [secDbName])
						WHEN [secServerPermission] = 'GRANT CONNECT SQL' THEN
							REPLACE(
								REPLACE(
									REPLACE(
										REPLACE(@sTemplateCreateLogin, '[VAR_CREATELOGIN]', @sTemplateServerGrant)
									, '[VAR_LOGINOPTIONS]', (CASE
												WHEN [secSid] IS NULL THEN ' FROM WINDOWS;'
												ELSE REPLACE(' WITH PASSWORD = ''Number@1'', SID=[VAR_SID],CHECK_EXPIRATION=OFF,CHECK_POLICY=OFF;', '[VAR_SID]', CONVERT(varchar(1024), [secSid], 1))
												END))
								, '[VAR_USERNAME]', [secUserName])
							, '[VAR_GRANTCMD]', [secServerPermission])
						WHEN [secServerPermission] IS NOT NULL THEN
							REPLACE(
								REPLACE(
									REPLACE(
										REPLACE(@sTemplateDbGrant, '[VAR_OBJECTEXISTS]', (CASE
											WHEN [secObjectType] = 'ENDPOINT' THEN REPLACE(@sTemplateEndpointExists, '[VAR_OBJECTNAME]', [secObjectName])
											WHEN [secObjectType] = 'SCHEMA' THEN REPLACE(@sTemplateSchemaExists, '[VAR_OBJECTNAME]', [secObjectName])
											WHEN [secObjectType] = 'OBJECT' THEN REPLACE(@sTemplateObjectExists, '[VAR_OBJECTNAME]', [secObjectName])
											ELSE ''
											END))
									, '[VAR_USERNAME]', [secUserName])
								, '[VAR_GRANTCMD]', [secServerPermission])
							, '[VAR_DBNAME]', 'master')
						ELSE ''
						END) [addCmd]
	FROM		[dbo].[tblMaint_Security] t1
	LEFT JOIN	@tDbPerms t2 ON t1.[secUserName] = t2.[userName]
				AND t1.[secDbName] = t2.[dbName]
				AND ISNULL(t1.[secDbRole], '') = ISNULL(t2.[dbRole], '')
				AND ISNULL(t1.[secServerPermission], '') = ISNULL(t2.[dbPermission], '')
	WHERE		t2.[userName] IS NULL
				AND t1.[secDbName] IS NOT NULL
				AND t1.[secServerRole] IS NULL
				AND ISNULL(t1.[secDbName], '`') <> '*'				--- * is for ignore remove permissions only
				AND ISNULL(t1.[secServerPermission], '`') <> '*'	--- * is for ignore remove permissions only
	ORDER BY	(CASE	WHEN [secServerRole] IS NOT NULL THEN 1
						WHEN [secServerPermission] IS NOT NULL AND [secDbName] IS NULL THEN 2
						WHEN [secServerPermission] IS NOT NULL AND [secDbName] IS NOT NULL THEN 3
						WHEN [secDbName] IS NOT NULL AND [secDbRole] IS NULL THEN 4
						ELSE 10
						END),
				[secServerRole],
				[secServerPermission],
				[secDbName],
				[secDbRole],
				[secUserName]
OPEN crsAdd
FETCH NEXT FROM crsAdd INTO @sCmd
WHILE @@FETCH_STATUS <> -1
  BEGIN
	SET @isExecute = 1;

	IF SUBSTRING(@sCmd, 1, 4) = 'USE '
		IF NOT EXISTS (SELECT TOP 1 [name] FROM [sys].[databases] WHERE [name] = LTRIM(RTRIM(REPLACE(REPLACE(SUBSTRING(@sCmd, 5, CHARINDEX(';', @sCmd) - 5), '[', ''), ']', ''))))
			SET @isExecute = 0
		ELSE IF [dbo].[fnConfig_IsDatabaseReadOnly](LTRIM(RTRIM(REPLACE(REPLACE(SUBSTRING(@sCmd, 5, CHARINDEX(';', @sCmd) - 5), '[', ''), ']', '')))) = 1
			SET @isExecute = 0

	IF @isExecute = 1
	BEGIN
		RAISERROR(@sCmd, 0, 1) WITH NOWAIT
		IF ISNULL(@reportOnly, 0) = 0
			EXEC	(@sCmd)
	END

	FETCH NEXT FROM crsAdd INTO @sCmd
  END
CLOSE crsAdd
DEALLOCATE crsAdd

END

BEGIN --- Remove unapproved/extra Database permissions

RAISERROR ('Remove unapproved/extra Database permissions', 0, 1) WITH NOWAIT
DECLARE crsRemove CURSOR FOR
	SELECT		REPLACE(REPLACE(t2.SqlCommand, 'sp_add', 'sp_drop'), 'GRANT', 'REVOKE') [dropCmd]
	FROM		[dbo].[tblMaint_Security] t1
	RIGHT JOIN	@tDbPerms t2 ON t1.[secUserName] = t2.[userName]
				AND ISNULL(t1.[secDbRole], '') = ISNULL(t2.[dbRole], '')
				AND (
					t1.[secDbName] = t2.[dbName]
					OR t1.[secDbName] = '*'				--- ignore remove permissions
				)
				AND (
					ISNULL(t1.[secServerPermission], '') = ISNULL(t2.[dbPermission], '')
					OR t1.[secServerPermission] = '*'	--- ignore remove permissions
				)
	WHERE		t1.[secUserName] IS NULL
	ORDER BY	(CASE	WHEN [secServerRole] IS NOT NULL THEN 1
						WHEN [secServerPermission] IS NOT NULL AND [secDbName] IS NULL THEN 2
						WHEN [secServerPermission] IS NOT NULL AND [secDbName] IS NOT NULL THEN 3
						WHEN [secDbName] IS NOT NULL AND [secDbRole] IS NULL THEN 4
						ELSE 10
						END),
				[secServerRole],
				[secServerPermission],
				[secDbName],
				[secDbRole],
				[secUserName]
OPEN crsRemove
FETCH NEXT FROM crsRemove INTO @sCmd
WHILE @@FETCH_STATUS <> -1
  BEGIN
	SET @isExecute = 1;

	IF SUBSTRING(@sCmd, 1, 4) = 'USE '
		IF NOT EXISTS (SELECT TOP 1 [name] FROM [sys].[databases] WHERE [name] = LTRIM(RTRIM(REPLACE(REPLACE(SUBSTRING(@sCmd, 5, CHARINDEX(';', @sCmd) - 5), '[', ''), ']', ''))))
			SET @isExecute = 0
		ELSE IF [dbo].[fnConfig_IsDatabaseReadOnly](LTRIM(RTRIM(REPLACE(REPLACE(SUBSTRING(@sCmd, 5, CHARINDEX(';', @sCmd) - 5), '[', ''), ']', '')))) = 1
			SET @isExecute = 0

	IF @isExecute = 1
	BEGIN
		RAISERROR(@sCmd, 0, 1) WITH NOWAIT
		IF ISNULL(@reportOnly, 0) = 0
			EXEC	(@sCmd)
	END

	FETCH NEXT FROM crsRemove INTO @sCmd
  END
CLOSE crsRemove
DEALLOCATE crsRemove

END

BEGIN --- Remove users from a Database that has no permissions

RAISERROR ('Remove users from a Database that has no permissions', 0, 1) WITH NOWAIT
EXEC	[dbo].[sp_MSforeachdb] @sDropDbUsersNoRights

END

BEGIN --- Remove logins that do have database level permissions

RAISERROR ('Remove logins that do have database level permissions', 0, 1) WITH NOWAIT

--- Get a list of logins without server level permissions
INSERT INTO #tNoPerm_LoginReview
	SELECT		t1.[name],
				t1.[sid]
	FROM		[sys].[server_principals] t1
	LEFT JOIN	[sys].[server_role_members] t2 ON t1.[principal_id] IN (t2.[member_principal_id], t2.[role_principal_id])
	LEFT JOIN	[sys].[server_permissions] t3 ON t1.[principal_id] = t3.[grantee_principal_id]
				AND t3.[type] NOT IN ('COSQ')
	WHERE		t1.[type] <> 'C'
				AND NOT (
					t1.[name] IN (SELECT [loginName] FROM @tStandardLogins)
					OR t1.[name] IN ('public', 'sa')
					OR t1.[name] LIKE '##%'
					OR t1.[name] LIKE 'NT SERVICE\%'
					OR t1.[name] LIKE 'NT AUTHORITY\%'
				)
				AND t1.[principal_id] > 10

				AND t2.[member_principal_id] IS NULL
				AND t2.[role_principal_id] IS NULL
				AND t3.[grantee_principal_id] IS NULL

--- Remove logins that do have database level permissions
EXEC	[dbo].[sp_MSforeachdb] @sDropLoginsSQL

END

BEGIN --- Delete the remaining logins that do not have permissions on the server

RAISERROR ('Delete the remaining logins that do not have permissions on the server', 0, 1) WITH NOWAIT
IF EXISTS (SELECT TOP 1 * FROM #tNoPerm_LoginReview)
BEGIN
	DECLARE crsDropLogin CURSOR READ_ONLY FAST_FORWARD FOR
		SELECT REPLACE('DROP LOGIN [[VAR_LOGINAME]];', '[VAR_LOGINAME]', [userName]) sCmd FROM #tNoPerm_LoginReview ORDER BY [userName]
	OPEN crsDropLogin
	FETCH NEXT FROM crsDropLogin INTO @sCmd
	WHILE @@FETCH_STATUS <> -1
	BEGIN
		RAISERROR(@sCmd, 0, 1) WITH NOWAIT
		IF ISNULL(@reportOnly, 0) = 0
			EXEC	(@sCmd)
		FETCH NEXT FROM crsDropLogin INTO @sCmd
	END
	CLOSE crsDropLogin
	DEALLOCATE crsDropLogin
END

END

BEGIN --- Ensure SQL Logins with a password hash have the correct password

DECLARE crsResetPwd CURSOR FAST_FORWARD READ_ONLY FOR
	--- Get the command using the desired password
	SELECT		[secUserName],
				REPLACE(
					REPLACE(@sTemplateSQLloginPwd, '[VAR_LOGINNAME]', QUOTENAME([secUserName]))
				, '[VAR_PASSWORD]', CONVERT(varchar(1024), [secHshPwd], 1))
	FROM		[dbo].[tblMaint_Security]
	WHERE		[secHshPwd] IS NOT NULL
OPEN crsResetPwd
FETCH NEXT FROM crsResetPwd INTO @LoginName, @sUpdateLogin
WHILE @@FETCH_STATUS <> -1
BEGIN
	--- Get the command using the actual password
	SELECT		@sAlterLogin =	REPLACE(
									REPLACE((CASE WHEN [type] = 'S' THEN @sTemplateSQLloginPwd ELSE '' END), '[VAR_LOGINNAME]', QUOTENAME([name]))
								, '[VAR_PASSWORD]', ISNULL(CONVERT(varchar(1024), LOGINPROPERTY([name], 'PASSWORDHASH'), 1), ''))
	FROM		[sys].[server_principals]
	WHERE		[type] IN ('S', 'G', 'U')
				AND [is_disabled] = 0
				AND [name] = @LoginName

	--- If the current server password is different than the desired server password - change it.
	IF @sAlterLogin <> @sUpdateLogin
	BEGIN
		RAISERROR(@sUpdateLogin, 0, 1) WITH NOWAIT
		IF ISNULL(@reportOnly, 0) = 0
			EXEC	(@sUpdateLogin)
	END

	FETCH NEXT FROM crsResetPwd INTO @LoginName, @sUpdateLogin
END
CLOSE crsResetPwd
DEALLOCATE crsResetPwd

END

--- Drop temporary table
IF OBJECT_ID('tempdb..#tNoPerm_LoginReview') IS NOT NULL
	DROP TABLE #tNoPerm_LoginReview;

SET NOCOUNT OFF;